//
//  YSSecuritiesDB.m
//  GuBoShi
//
//  Created by wangsu on 2017/3/30.
//  Copyright © 2017年 YiShi. All rights reserved.
//

#import "YSSecuritiesDB.h"
#import "YSSecurities.h"
#import <YTXUtilCategory/NSDictionary+YTXUtilCategory.h>
#import <YTXUtilCategory/NSString+YTXUtilCategory.h>

#import <FMDB/FMDB.h>

NSString * const SecuritiesTableName = @"YSSecurities";
NSString * const FavoriteSecuritiesTableName = @"YSFavoriteSecurities";
NSString * const CodeVersionTableName = @"YSCodeVersion";

NSString * const DeleteCJQuoteSQL = @"DELETE FROM YSSecurities;";


NSString * const InsertSecurities = @"INSERT INTO YSSecurities (_Id, _market, _number, _name, _namePinYin, _minuteSpan, _stall, _type, _status, _isSubscribe, _isAdded, _ProId, _ProTp, _DeliUnit, _RefPri, _Tick, _MinChgAmt, _DeliDate, _StaDelDate, _TraDate, _detailJsonStr)\
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";


NSString * const InsertFavoriteSecurities = @"INSERT INTO YSFavoriteSecurities (_Id, _market, _number, _name, _namePinYin, _minuteSpan, _stall, _type, _status, _isSubscribe, _isDelete)\
VALUES (?,?,?,?,?,?,?,?,?,?,?);";

NSString * const getFavoriteDatas = @"SELECT * FROM YSFavoriteSecurities WHERE _isDelete = %@";
NSString * const getALLSecuritiesDatas = @"SELECT * FROM YSSecurities";


NSString * const deleteDataFromFavoriteLists = @"DELETE FROM YSFavoriteSecurities where _id = (?)";

NSString *const UpdateSecurities = @"UPDATE YSSecurities SET _isAdded = (?) WHERE _number = (?);";

NSString *const UpdateCodeVersion = @"UPDATE YSCodeVersion SET _codeVersion = (?);";

NSString *const InsertCodeVersion = @"INSERT INTO YSCodeVersion (_codeVersion) VALUES (?);";

NSString *const DeleteCodeVersion = @"DELETE _codeVersion FROM YSCodeVersion;";

NSString *const QuerySecuritiesName = @"select _id,_market,_number,_name,_namePinYin,_minuteSpan,_stall,_type,_status,_isSubscribe,_isAdded,_detailJsonStr from(select t._id,t._market,t._number,t._name,t._namePinYin,t._minuteSpan,t._stall,t._type,t._status,t._isSubscribe,_isAdded,_detailJsonStr,0 order_num from YSSecurities t where t._name='%@' union select s._id,s._market,s._number,s._name,s._namePinYin,s._minuteSpan,s._stall,s._type,s._status,s._isSubscribe,_isAdded,_detailJsonStr,1 order_num from  YSSecurities s where s._name like '%%%@%%' and s._name<>'%@')aa order by order_num,_namePinYin limit 0,%@;";

NSString *const QuerySecuritiesPinYin = @"select _id,_market,_number,_name,_namePinYin,_minuteSpan,_stall,_type,_status,_isSubscribe,_isAdded,_detailJsonStr from(select t._id,t._market,t._number,t._name,t._namePinYin,t._minuteSpan,t._stall,t._type,t._status,t._isSubscribe,_isAdded,_detailJsonStr,0 order_num from YSSecurities t where t._namePinYin='%@' union select s._id,s._market,s._number,s._name,s._namePinYin,s._minuteSpan,s._stall,s._type,s._status,s._isSubscribe,_isAdded,_detailJsonStr,1 order_num from  YSSecurities s where s._namePinYin like '%%%@%%' and s._namePinYin<>'%@')aa order by order_num,_namePinYin limit 0,%@;";

NSString *const QuerySecuritiesNumber = @"select _id,_market,_number,_name,_namePinYin,_minuteSpan,_stall,_type,_status,_isSubscribe,_isAdded,_detailJsonStr from(select t._id,t._market,t._number,t._name,t._namePinYin,t._minuteSpan,t._stall,t._type,t._status,t._isSubscribe,_isAdded,_detailJsonStr,0 order_num from YSSecurities t where t._number='%@' union select s._id,s._market,s._number,s._name,s._namePinYin,s._minuteSpan,s._stall,s._type,s._status,s._isSubscribe,_isAdded,_detailJsonStr,1 order_num from YSSecurities s where s._number like '%%%@%%' and s._number<>'%@')aa order by order_num,_number limit 0,%@;";

NSString *const QuerySecuritiesNumberAndName = @"select s._id,s._market,s._number,s._name,s._namePinYin,s._minuteSpan,s._stall,s._type,s._status,s._isSubscribe,_isAdded,_detailJsonStr from YSSecurities s where s._number like '%%%@%%' or s._name like '%%%@%%' order by _number limit 0,%@;";

NSString *const QuerySecuritiesMarket = @"select _id,_market,_number,_name,_namePinYin,_minuteSpan,_stall,_type,_status,_isSubscribe,_isAdded from(select t._id,t._market,t._number,t._name,t._namePinYin,t._minuteSpan,t._stall,t._type,t._status,t._isSubscribe,_isAdded,0 order_num from YSSecurities t where t._market='%@' union select s._id,s._market,s._number,s._name,s._namePinYin,s._minuteSpan,s._stall,s._type,s._status,s._isSubscribe,_isAdded,1 order_num from YSSecurities s where s._market like '%%%@%%' and s._market<>'%@')aa order by order_num,_number limit 0,%@;";

NSString *const QuerySecuritiesEi = @"select _id,_market,_number,_ProId,_name,_namePinYin,_minuteSpan,_stall,_type,_status,_isSubscribe,_isAdded,_detailJsonStr from YSSecurities where _id = %@ limit 0,1;";

NSString *const QueryDetailSecuritiesName = @"select _id,_market,_number,_name,_namePinYin,_minuteSpan,_stall,_type,_status,_isSubscribe,_isAdded,_detailJsonStr from YSSecurities where _name = %@ limit 0,1;";

NSString *const QuerySecuritiesEiList = @"select _id,_market,_number,_name,_namePinYin,_minuteSpan,_stall,_type,_status,_isSubscribe,_isAdded from YSSecurities where _id in (%@);";

NSString *const QueryStockMarket = @"select _id,_market,_number,_name,_namePinYin,_minuteSpan,_stall,_type,_status,_isSubscribe,_isAdded from YSSecurities where _type = %@;";


NSString *const QueryCodeVersion = @"select * from YSCodeVersion limit 0,1;";

NSString *const QueryTransferParameter = @"select _id, _ProId, _ProTp, _DeliUnit, _RefPri, _Tick, _MinChgAmt, _DeliDate, _StaDelDate, _TraDate,_detailJsonStr from YSSecurities where _id = %@ limit 0,1;";


/** 创建马表盘 */
NSString * const createSecuritiesSQL =
@"_Id  PRIMARY KEY NOT NULL UNIQUE,_market TEXT,_number TEXT,_name TEXT,_namePinYin TEXT,_minuteSpan REAL,\
_stall REAL,_type REAL,_status REAL,_isSubscribe REAL,_isAdded TEXT,_ProId TEXT,_ProTp TEXT,_DeliUnit TEXT,_RefPri REAL,_Tick REAL,_MinChgAmt REAL,_DeliDate TEXT, _StaDelDate TEXT, _TraDate TEXT,_detailJsonStr TEXT";

/** 创建码表版本 */
NSString *const createCodeVersionSQL = @"_codeVersion PRIMARY KEY NOT NULL UNIQUE";

/** 创建收藏码表 */
NSString * const createFavoriteSQL = @"_Id  PRIMARY KEY NOT NULL UNIQUE,\
_market TEXT,\
_number TEXT,\
_name TEXT,\
_namePinYin TEXT,\
_minuteSpan REAL,\
_stall REAL,\
_type REAL,\
_status REAL,\
_isSubscribe REAL,\
_isDelete REAL";


@interface YSSecuritiesDB ()

/** 数据库队列 */
@property (nonatomic, strong) FMDatabaseQueue *dbQueue;

/** 数据库位置 */
@property (nonatomic, copy) NSString *dbPath;

@end

@implementation YSSecuritiesDB

+ (instancetype) sharedManager {
    static YSSecuritiesDB *sharedManager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        sharedManager = [[self alloc] init];
    });
    return sharedManager;
}

#pragma mark - life cycle
- (instancetype)init
{
    self = [super init];
    if (self) {
        [self createTableIfNotExit];
    }
    return self;
}

#pragma mark - private 创建表
- (void)createTableIfNotExit {
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSLog(@"数据库打开 :%@",self.dbPath);
            if (![self checkTableExist:SecuritiesTableName withFMData:db]) {
                [self createTable:SecuritiesTableName withArguments:createSecuritiesSQL withFMData:db];
            }
            
            if (![self checkTableExist:FavoriteSecuritiesTableName withFMData:db]) {
                [self createTable:FavoriteSecuritiesTableName withArguments:createFavoriteSQL withFMData:db];
            }
            
            if (![self checkTableExist:CodeVersionTableName withFMData:db]) {
                [self createTable:CodeVersionTableName withArguments:createCodeVersionSQL withFMData:db];
            }
        }
        [db close];
    }];
}

- (BOOL)createTable:(NSString *)tableName withArguments:(NSString *)arguments withFMData:(FMDatabase *)dbDatabase
{
    NSString *sqlstr = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' (%@);", tableName, arguments];
    if (![dbDatabase executeUpdate:sqlstr]){
        return NO;
    }
    return YES;
}

- (BOOL) checkTableExist:(NSString *)tableName withFMData:(FMDatabase *)dbDatabase {
    
    BOOL result;
    
    tableName = [tableName lowercaseString];
    
    FMResultSet *rs = [dbDatabase executeQuery:@"select [sql] from sqlite_master where [type] = 'table' and lower(name) = ?", tableName];
    
    result = [rs next];
    
    [rs close];
    
    return result;
    
}


#pragma mark - 增
- (BOOL)insertData:(NSArray<YSSecurities*>*)data {
    
    __block BOOL result = YES;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            for (YSSecurities *item in data) {
                NSDictionary *detailDic = [MTLJSONAdapter JSONDictionaryFromModel:item];
                NSString *detailJsonStr = [detailDic toJSONString];
                result = [db executeUpdate:InsertSecurities,item.Id,item.market,item.number,item.name,item.namePinYin,item.minuteSpan,item.stall,item.type,item.status,item.isSubscribe,item.isAdded, item.ProId, item.ProTp, item.DeliUnit, item.RefPri, item.Tick, item.MinChgAmt, item.DeliDate, item.StaDelDate, item.TraDate, detailJsonStr];
            }
        }
        [db close];
    }];
    return result;
}


- (BOOL)isCanInsterFavoriteSecurities{
    return [self queryFavoriteSecuritiesCount] >= 11 ? NO : YES ;
}

- (BOOL)insertDataToFavoriteList:(YSSecurities* )item{

    __block BOOL result = YES;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            
            result = [db executeUpdate:InsertFavoriteSecurities,item.Id,item.market,
                 item.number,item.name,
                 item.namePinYin,item.minuteSpan,item.stall,item.type,item.status,@(item.isSubscribe),@(0)];
        }
        [db close];
    }];
    return result;
}

- (NSInteger )queryFavoriteSecuritiesCount{
    return [[self queryFavoriteSecurities] count];
}

- (NSArray<YSSecurities *> *)queryFavoriteSecurities
{
    __block NSMutableArray <YSSecurities *>* resultArr = [@[] mutableCopy];
    
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            NSString *sqlStr = [NSString stringWithFormat:getFavoriteDatas,@"0"];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                [resultArr addObject:[self transforDataSecurities:resultSet]];
            }
        }
         [db close];
    }];
    return [NSArray arrayWithArray:resultArr];
}

- (NSArray<YSSecurities *> *)queryAllSecurities
{
    __block NSMutableArray <YSSecurities *>* resultArr = [@[] mutableCopy];
    
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            NSString *sqlStr = getALLSecuritiesDatas;
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                [resultArr addObject:[self transforDetailSecurities:resultSet]];
            }
        }
        [db close];
    }];
    return [NSArray arrayWithArray:resultArr];
}


- (BOOL)deleteQuoteData {
    __block BOOL isSuccess = YES;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            isSuccess = [db executeUpdate:DeleteCJQuoteSQL];
        }
        [db close];
    }];
    return isSuccess;
}


- (BOOL)deleteFavoriteSecuritiesModels:(NSArray<YSSecurities *> *)SecuritiesList{

    __block BOOL result = YES;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            for (YSSecurities *item in SecuritiesList) {
                [db executeUpdate:deleteDataFromFavoriteLists,item.Id];
            }
        }
        [db close];
    }];
    return result;
}

#pragma mark - 改
- (BOOL)updateDataToListAdded:(NSArray<YSSecurities*>*)data {
    
    __block BOOL result = YES;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            for (YSSecurities *item in data) {
             result = [db executeUpdate:UpdateSecurities,item.isAdded,item.number];
            }
        }
        [db close];
    }];
    return YES;
}

- (BOOL)updateCodeVersion:(NSString *)codeVersion{
    
    NSString *codeStr = [self getCodeVersion];
    __block BOOL result = NO;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            if (codeStr && codeStr.length) {
                 result = [db executeUpdate:UpdateCodeVersion,codeVersion];
            }else{
                 result = [db executeUpdate:InsertCodeVersion,codeVersion];
            }
        }
        [db close];
    }];
    if (!result) {
        LGlog(@"__errer__码表版本号插入失败");
    }
    return result;
}


#pragma mark - 查
- (NSString *)getCodeVersion {
    __block NSString *codeVersion = @"";
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            FMResultSet *resultSet = [db executeQuery:QueryCodeVersion];
            while ([resultSet next]) {
                codeVersion = [resultSet stringForColumn:@"_codeVersion"];
            }
            [resultSet close];
        }
        [db close];
    }];
    return codeVersion;
}


- (NSArray *)getDataWithNameFromDB:(NSString *)name limit:(NSString *)limit{
    __block NSMutableArray *resultArr = [@[] mutableCopy];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSString *sqlStr = [NSString stringWithFormat:QuerySecuritiesName,name,name,name,limit];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                [resultArr addObject:[self transforDataSecurities:resultSet]];
            }
            [resultSet close];
        }
        [db close];
    }];
    return [NSArray arrayWithArray:resultArr];
}

- (NSArray *)getDataWithPinYinFromDB:(NSString *)name limit:(NSString *)limit{
    __block NSMutableArray *resultArr = [@[] mutableCopy];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSString *sqlStr = [NSString stringWithFormat:QuerySecuritiesPinYin,name,name,name,limit];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                [resultArr addObject:[self transforDataSecurities:resultSet]];
            }
            [resultSet close];
        }
        [db close];
    }];
    return [NSArray arrayWithArray:resultArr];
}

- (NSArray *)getDataWithNumberFromDB:(NSString *)name limit:(NSString *)limit {
    __block NSMutableArray *resultArr = [@[] mutableCopy];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSString *sqlStr = [NSString stringWithFormat:QuerySecuritiesNumber,name,name,name,limit];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                [resultArr addObject:[self transforDetailSecurities:resultSet]];
            }
            [resultSet close];
        }
        [db close];
    }];
    return [NSArray arrayWithArray:resultArr];
}

- (NSArray *)getDataWithNumberAndNameFromDB:(NSString *)name limit:(NSString *)limit {
    __block NSMutableArray *resultArr = [@[] mutableCopy];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSString *sqlStr = [NSString stringWithFormat:QuerySecuritiesNumberAndName,name,name,limit];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                [resultArr addObject:[self transforDetailSecurities:resultSet]];
            }
            [resultSet close];
        }
        [db close];
    }];
    return [NSArray arrayWithArray:resultArr];
}

- (NSArray *)getDataWithMarketFromDB:(NSString *)name limit:(NSString *)limit {
    __block NSMutableArray *resultArr = [@[] mutableCopy];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSString *sqlStr = [NSString stringWithFormat:QuerySecuritiesMarket,name,name,name,limit];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                [resultArr addObject:[self transforDataSecurities:resultSet]];
            }
            [resultSet close];
        }
        [db close];
    }];
    return resultArr;
}

- (YSSecurities *)getDataWithEiFromDB:(NSString *)name {
    
    __block YSSecurities *item = nil;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSString *sqlStr = [NSString stringWithFormat:QuerySecuritiesEi,name];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                item = [self transforDetailSecurities:resultSet];
            }
            [resultSet close];
        }
        [db close];
    }];
    return item;
}

- (YSSecurities *)getDataWithTransferParameterFromDB:(NSString *)dataId {
    __block YSSecurities *item = nil;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSString *sqlStr = [NSString stringWithFormat:QueryTransferParameter,dataId];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                item = [self transforDetailSecurities:resultSet];
            }
            [resultSet close];
        }
        [db close];
    }];
    return item;
}

- (NSArray *)getDatasWithEiListFromDB:(NSArray *)EiList {
    __block NSMutableArray *resultArr = [@[] mutableCopy];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSString *eiListStr = [EiList componentsJoinedByString:@","];
            NSString *sqlStr = [NSString stringWithFormat:QuerySecuritiesEiList,eiListStr];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                [resultArr addObject:[self transforDataSecurities:resultSet]];
            }
            [resultSet close];
        }
        [db close];
    }];
    return resultArr;
}

- (NSArray *)getAllStockMarketFromDB {
    
    __block NSMutableArray *resultArr = [@[] mutableCopy];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if([db open]){
            NSString *sqlStr = [NSString stringWithFormat:QueryStockMarket,@"3"];
            FMResultSet *resultSet = [db executeQuery:sqlStr];
            while ([resultSet next]) {
                [resultArr addObject:[self transforDataSecurities:resultSet]];
            }
            [resultSet close];
        }
        [db close];
    }];
    return [NSArray arrayWithArray:resultArr];
}



- (YSSecurities *)transforDetailSecurities:(FMResultSet *)resultSet {
    
    YSSecurities *item = [[YSSecurities alloc] init];
    NSString *detailStr = [resultSet stringForColumn:@"_detailJsonStr"];
    NSDictionary *detailDicStr = [detailStr jsonStringToDictionary];
    item = [MTLJSONAdapter modelOfClass:[YSSecurities class] fromJSONDictionary:detailDicStr error:nil];
    item.isAdded = [resultSet stringForColumn:@"_isAdded"];
    
    return item;
}

- (YSSecurities *)transforDataSecurities:(FMResultSet *)resultSet {
    
    YSSecurities *item = [[YSSecurities alloc] init];
    item.Id = [NSNumber numberWithLong:[resultSet longLongIntForColumn:@"_id"]];
    item.market = [resultSet stringForColumn:@"_market"];
    item.number = [resultSet stringForColumn:@"_number"];
    item.name = [resultSet stringForColumn:@"_name"];
    item.namePinYin = [resultSet stringForColumn:@"_namePinYin"];;;
    item.minuteSpan = [NSNumber numberWithLong:[resultSet intForColumn:@"_minuteSpan"]];
    //    item.stall = (YSSecuritiesStalls)[resultSet intForColumn:@"_stall"];
    //    item.type = (YSSecuritiesType)[resultSet intForColumn:@"_type"];
    //    item.status = (YSSecuritiesStatus)[resultSet intForColumn:@"_status"];
    item.stall = @([resultSet intForColumn:@"_stall"]);
    item.type = [resultSet stringForColumn:@"_type"];
    item.status = @([resultSet intForColumn:@"_status"]);
    item.isSubscribe = (YSSecuritiesSubcribeType)[resultSet intForColumn:@"_isSubscribe"];
//    item.isAdded = [resultSet stringForColumn:@"_isAdded"];
    
    return item;
}


#pragma mark - property
- (FMDatabaseQueue *)dbQueue{
    if (!_dbQueue) {
        _dbQueue = [FMDatabaseQueue databaseQueueWithPath:self.dbPath];
    }
    return _dbQueue;
}

- (NSString *)dbPath {
    if (!_dbPath) {
        NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
        _dbPath = [NSString stringWithFormat:@"%@/YSSecurities.db", path];
    }
    return _dbPath;
}
+ (NSString *)transform:(NSString *)chinese
{
    NSMutableString *pinyin = [chinese mutableCopy];
    CFStringTransform((__bridge CFMutableStringRef)pinyin, NULL, kCFStringTransformMandarinLatin, NO);
    CFStringTransform((__bridge CFMutableStringRef)pinyin, NULL, kCFStringTransformStripCombiningMarks, NO);
    NSLog(@"%@", pinyin);
    return [[pinyin uppercaseString] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
}
@end
